MySQL定义语言[DDL]

发表于 | 分类于

创建表table

1
2
3
4
5
6
create table table_name (
    列名 属性,
    age int,
    ...
    name varchar(10)
);

整型列

  • tinyint：1字节，取值范围[-128 ~ 127]，非负取值范围[0 ~ 255]

  • smallint：2字节，取值范围[-32768 ~ 32767]，非负取值范围[0 ~ 65535]

  • mediumint：3字节，取值范围[-8388608 ~ 8388607]，非负取值范围[0 ~ 16777215]

  • int：4字节，取值范围[-21亿 ~ 21亿]，非负取值范围[0 ~ 42亿]

  • bigint：8字节

整型例的可选参数【属性】

  • 各种整型数据类型默认为带符号的取值范围

  • unsigned：无符号，列的值从0开始，不为负。

  • [M] zerofill：适合用于学号，编码等固定宽度的数字，可以用0填充至固定宽度，并且默认决定列为unsigned属性。

如果数字位数超过了[M],并不影响，MySQL会字节存取。

1
alert table table_name add 列名 tinyint(5) zerofill;    添加一个固定宽度为5的列

浮点列与定点列

  • float[M,D]：浮点型，M表示精度，总位数；D表示标度，小数位数；这两个参数可以省略不写，默认为普通浮点数。

  • double[M,D]：与上相同，只是默认精度更高。

  • decimal[M,D]：定点型，float和double有精度损失，而decimal没有，更精确。

字符型列

类型 宽度 可存字符 实存字符[i<=M] 实占空间 利用率
char M M i M <=100%
varchar M M i i字符(+1-2)字节 <100%

  • char型如果不够M个字符，内部活用空格补齐，取出时再把右侧空格删掉。[这意味着，如果右侧本身有空格，将会丢失]，但是char速度更快。

  • M是字符，是几就表示可以存几个汉字。

  • text：文本型，可存储约6万字。

  • blob：用来存储二进制文件，如图片，音频等，由于采用的是二进制形式，不用考虑字符集。

  • enum：枚举型，是定义好值就在某几个枚举范围内。

1
2
3
4
alert table table_name add 列名 enum("男","女");
//只能选址一个值进行插入操作

alert table table_name add 列名 set("男","女");    //可以选择一个或多个值进行插入操作

日期时间型列

类型 样式 范围
year 1995 1901~2155
date 1998-12-31 1000/01/01~9999/12/31
time 12:32:45 -838:59:59 ~ 838:59:59
datetime 1998-12-31 12:32:45 -
timestamp 1512394064 可不写，系统自动获取

列的默认值

因为null查询不便且索引效率不高，所以在实际应用中，应尽量避免列的值为null，如果想避免，声明列not null default “默认值”

1
alert table table_name add 列名 int not null default 1;

主键与自增

主键：次列不重复，能够区分每一行。

1
2
3
4
5
6
7
8
9
10
11
12
//写法一
create table table_name(
    id int primary key,
    name char(10)
);

//写法二
create table table_name(
    id int,
    name char(10),
    primary key(id)
);

自增：一张表，只能有一个自增列，且此列必须加索引[index/key id]

1
2
3
4
5
create table table_name(
    id int,
    name char(10),
    index id(id)
);

主键且自增

1
2
3
4
create table table_name(
    id int primary key auto_increment,
    name char(10),
);

列的增删改

1
2
3
4
5
6
7
8
9
alert table 表名 add 列名 列类型 列属性；               //添加列，默认在表最后

alert table 表名 add 列名 列类型 列属性 after 列名；     //添加一个新列并放在某列之后

alert table 表名 drop 列名；                   //删除列

alert table 表名 change 列名 新列名 新列类型；          //修改列名和列类型

alert table 表名 modify 列名 新列类型；                //只能修改列类型

视图

1
create view  视图名 as select 列1, 列2, 列3 from table_name;

  • 如果一个查询结果集需要经常被用到，就需要创建视图。

  • 又被称为虚拟表，是sql的查询结果。

  • 作用：

    • 权限控制。

比如说某几个列允许用户查询，其他列不允许，可以通过视图开放其中一列或几列起到权限控制的作用。

  • 简化复杂的查询

  • 如果视图的所有列都来自物理表，那么视图中的数据就可以修改；如果视图中存在某一列或几列是经过物理表中的列进行运算得到的，那么视图中这样的列中的数据就不能被修改。

视图的algorithm [视图放在哪？]

1
2
3
4
5
6
7
8
//指定使用merge算法
create algorithm=merge view  视图名 as select 列1, 列2, 列3 from table_name;

////指定使用temptable算法
create algorithm=temptable view  视图名 as select 列1, 列2, 列3 from table_name;

//由MySQL自己决定使用何种算法
create view  视图名 as select 列1, 列2, 列3 from table_name;

  • 对于简单查询形成的view，再对view进行查询时，如where，order等等，可以把建视图语句+查视图语句合并成 –> 查物理的语句。这种视图的算法叫merge [合并]

  • 也有可能创建视图的语句本身就比较复杂，很难在和查询视图的语句进行合并，MySQL可以先执行视图的创建语句，把结果集形成内存中的临时表，然后去查询临时表。这种视图的算法叫temptable [临时表]

常用表管理语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
show tables;                                //查看所有表,视图也会包含在内。

desc 表名;                                   //查看表详细信息

drop table 表名;                             //删除表

drop view 视图名;                            //删除视图

show create table 表名/视图名;                //查看建表/视图过程

show table status \G;                       //查看所有表详细信息，\G表示竖排显示

show table status where name = '表名' \G;    //查看指定表详细信息

rename table 旧表名 to 新表名;                //改表名

truncate 表名;                               //想当于删除后有重建了这个表

delete from 表名;    //删除表中所有数据，但如果继续插入数据，自增列会在原有基础上继续自增

存储引擎的概念

1
2
3
4
create table table_name(
    id int,
    name char(10)
)engine innodb charset utf8;

  • 数据库对同样的数据有着不同的存储方式和管理方式，在MySQL中称之为数据引擎

  • 在MySQL_5.5.5以前默认引擎是Myisam，从MySQL_5.5.5开始，默认引擎改为Innodb

  • Myisam数据组织方式：每一个数据库形成一个文件夹，每张表包含3个文件放在该文件夹下

    • table_name.frm //表结构文件

    • table_name.MYD //表数据文件

    • table_name.MYI //表索引文件

    • 可以直接把文件夹拷走，就是一个完整的数据库

    • Myisam不支持事务

    • Innodb数据组织方式：所有数据放在一个文件内

字符集与乱码问题

1
2
3
4
create table table_name(
    id int,
    name char(10)
)engine innodb charset utf8;

  • 乱码是因为文字本来的的字符集与展示的字符集不一致导致

  • 校对集：就是排序规则

  • 一套字符集可以有几套校对集

image

1
2
3
4
5
character_set_client = 'utf8'           //设置客户端发送与可接收字符集
character_set_connection = 'utf8'       //设置解释字符集
character_set_results = 'utf8'          //设置数据库返回与可接收字符集

set names utf8;                         //就是将以上三个变量同时设为utf8字符集

  • 解释器可以将不同编码进行翻译

  • 如果客户端和MySQL使用的是不同的字符集，就会出现乱码

  • 连接器使用GBK或者UTF8都可以，但是不可以使用编码范围比较小的字符集，比如ASCII，那样会造成数据丢失

  • 谁能连接数据库谁就是客户端，如PHP和MySQL的控制窗口就是两个不同的客户端

  • 保证HTML文件，数据库，处理脚本（如PHP），查询语句前的声明保持一致，就可以避免乱码

索引的概念

1
2
3
4
5
6
7
8
9
10
11
12
13
key                //普通索引，纯粹为了加快查询速度

unique key         //唯一所以，一是为了加快查询速度，二是为了约束数据 [不能重复]

primary key        //主键索引，不需要起名，因为一张表就一个

fulltext           //全文索引，在中文环境下，几乎无效
create table table_name(
    id int,
    name char(10),
    key name(name),
    unique id(id)
);

  • 索引是数据的目录，能快速定位行数据的位置，索引提高了查询速度，但是影响了增删改的速度，并非加的越多越好，一般在查询频率比较高的列上加，而且在重复度的列上加效果更好

  • 索引长度：建索引时，可以只索引列的前一部分内容，比如前10个字符

1
2
3
4
5
create table table_name(
   id int,
   name char(10),
   key name(name(5))
);
  • 多列索引：就是把2列或多列的值，看成一个整体然后索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
create table table_name(
   xing char(5),
   ming char(10),
   key xm(xing,ming)
);

insert into table_name values ('朱','元璋');                     //插入一条数据

select * from table_name where xing = '朱' and ming = '元璋';    //索引生效

select * from table_name where xing = '朱';                     //索引生效

select * from table_name where ming = '元璋';                    //索引失效

  • 索引是左前缀发挥作用

  • 冗余索引：就是在某个列上，可能存在多个索引

1
2
3
4
5
6
create table table_name(
   xing char(5),
   ming char(10),
   key xm(xing,ming),
   key ming(ming)
);

索引的操作语法

1
2
3
4
5
6
7
8
9
show index from 表名; || show create table 表名;                     //查看索引

alter table 表名 drop index 索引名;  ||  drop index 索引名 on 表名;    //删除索引

alter table 表名 add 索引名(列名);                                    //添加索引

alter table 表名 add primary key (列名);                             //添加主键索引

alter table 表名 drop primary key;                                   //删除主键所以

事务的概念

1
2
3
4
5
6
7
8
9
10
11
//事务执行成功
start transaction
update 表名 set money+500 where id=2;
update 表名 set money-500 where id=1;
commit;

//事务执行失败，进行回滚
start transaction
update 表名 set money+500 where id=2;
update 表名 set money-500 where id=1;
rollback;

  • 比如转账就可以看作一个事务，包含A扣款，B收款两个部分，只有两部分都成功，才能说一个事务完成

  • 事务具有以下四个特点

    • 隔离性：用户看不到事务的中间态

    • 原子性：不论事务中间过程过么复杂，最后结果要么是事务执行之前的状态，要么是事务执行成功的状态

    • 一致性：事务前后的数据保持守恒

    • 持久性：事务一旦执行成功，就不能rollback